﻿using FixtureManageSystem.Views;
using Log.Log;
using MySql.Data.MySqlClient;
using MySqlX.XDevAPI.Common;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Math;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Data.SQLite;

namespace FixtureManageSystem.Models
{
    public class EmployeeService
    {
        public static List<Employee> _employees;
 
        public SqliteHelper sqliteHelper = new SqliteHelper();

        public EmployeeService()
        {
            InquireEmployees(1, 10);
        }

        // 获取员工的总数
        public  int GetEmployeesCount()
        {
            // 创建一变量来存储员工数量
            int count;
            // 创建要执行的Sql 语句
            string str = "SELECT COUNT(*) as totalNumber FROM Employee";
            // 2、执行SQL语句并接受结果
            SQLiteDataReader Sdr = sqliteHelper.read(str);

            // 3、读取结果的数据
            while (Sdr.Read())
            {
                count = Convert.ToInt32(Sdr["totalNumber"]);
                return count;
            }
            return 0;
        }
        public List<Employee> GetEmployees()
        {
            return _employees;
        }


        /// <summary>
        /// 查询第 page 页，pagesize数量的条目，其实仅需改变列表_employees就行
        /// </summary>
        public ObservableCollection<Employee> BrowseEmployees(int page, int pageSize)
        {
            ObservableCollection<Employee> employees = new ObservableCollection<Employee>();
            // 创建要执行的Sql 语句
            string str = $"select * from Employee  order by ID  limit {pageSize} OFFSET {(page - 1) * pageSize} ;";
            // 2、执行SQL语句并接受结果
            SQLiteDataReader Sdr = sqliteHelper.read(str);
            // 4、读取结果的数据
            while (Sdr.Read())
            {

                employees.Add(new Employee()
                {
                    ID =Convert.ToInt32(Sdr["ID"]),
                    PersonNo = Sdr["PersonNo"].ToString(),
                    Name = Sdr["Name"].ToString(),
                    Department = Sdr["Department"].ToString(),
                    Factory = Sdr["Factory"].ToString(),
                    Tel = Sdr["Tel"].ToString()
                });

            }
            return employees;
        }

        /// <summary>
        /// 查询第 page 页，pagesize数量的条目，其实仅需改变列表_employees就行
        /// </summary>
        public void InquireEmployees(int page, int pageSize)
        {

            _employees = new List<Employee>();
            // 创建要执行的Sql 语句
            string str = $"select * from Employee  order by ID  limit {pageSize} OFFSET {(page - 1) * pageSize} ;";
            // 2、执行SQL语句并接受结果
            SQLiteDataReader Sdr = sqliteHelper.read(str);
            // 4、读取结果的数据
            while (Sdr.Read())
            {

                _employees.Add(new Employee()
                {
                    ID = Convert.ToInt32(Sdr["ID"]),
                    PersonNo = Sdr["PersonNo"].ToString(),
                    Name = Sdr["Name"].ToString(),
                    Department = Sdr["Department"].ToString(),
                    Factory = Sdr["Factory"].ToString(),
                    Tel = Sdr["Tel"].ToString()
                });

            }

        }

        /// <summary>
        /// 新增员工功能
        /// </summary>
        public bool Add(Employee employee)
        {

            // 创建要执行的Sql 语句
            string str = "INSERT INTO Employee(PersonNo, Name, Department, Factory, Tel) VALUES" +
                         $"('{employee.PersonNo}', '{employee.Name}','{employee.Department}','{employee.Factory}', '{employee.Tel}')";
            int r = sqliteHelper.Execute(str);
            return r > 0;
        }


        /// <summary>
        /// 改
        /// </summary>
        public bool Update(Employee employee)
        {

            // 创建要执行的Sql 语句
            string str = $"UPDATE Employee SET Name = '{employee.Name}', Department = '{employee.Department}', " +
                         $"Factory = '{employee.Factory}', Tel = '{employee.Tel}' , PersonNo = '{employee.PersonNo}' " +
                         $"WHERE ID = {employee.ID}";
            int r = sqliteHelper.Execute(str);
            return r > 0;
        }

        public Employee GetEmployee(int ID)
        {
            string str = $"SELECT * FROM EMPLOYEE WHERE ID = {ID}";
            Employee result = null;
            SQLiteDataReader Sdr = sqliteHelper.read(str);
            while (Sdr.Read())
            {
                result = new Employee()
                {
                    ID = Convert.ToInt32(Sdr["ID"]),
                    PersonNo = Sdr["PersonNo"].ToString(),
                    Name = Sdr["Name"].ToString(),
                    Department = Sdr["Department"].ToString(),
                    Factory = Sdr["Factory"].ToString(),
                    Tel = Sdr["Tel"].ToString()
                };
            }
            return result;
        }

        public bool Delete(int ID)
        {
            // 创建要执行的Sql 语句
            string str = $"DELETE FROM Employee WHERE ID = {ID}";
            int r = sqliteHelper.Execute(str);
            return r > 0;
        }


        // 根据工号查询员工信息、根据姓名、电话号、工号
        public ObservableCollection<Employee> GetEmployee(Employee em)
        {
            string personNo = em.PersonNo;
            string peronsName = em.Name;
            string telNo = em.Tel;

            // 返回的结果
            ObservableCollection< Employee > result = new ObservableCollection<Employee>();

            if (string.IsNullOrEmpty(personNo) && string.IsNullOrEmpty(peronsName) && string.IsNullOrEmpty(telNo))
                return result;

            // 创建要执行的Sql 语句
            string str = $"select * from Employee Where ";
            List<string> conditions = new List<string>();
            if (!string.IsNullOrEmpty(personNo))
            {
                conditions.Add($" PersonNo Like '%{personNo}%' ");
            }
            if (!string.IsNullOrEmpty(peronsName))
            {
                conditions.Add($" Name Like '%{peronsName}%' ");
            }
            if (!string.IsNullOrEmpty(telNo))
            {
                conditions.Add($" Tel Like '%{telNo}%' ");
            }
            str += string.Join(" OR ", conditions);

            SQLiteDataReader Sdr = sqliteHelper.read(str);

            int count = 0;
            // 4、读取结果的数据
            while (Sdr.Read())
            {

                result.Add(new Employee()
                {
                    ID = Convert.ToInt32(Sdr["ID"]),
                    PersonNo = Sdr["PersonNo"].ToString(),
                    Name = Sdr["Name"].ToString(),
                    Department = Sdr["Department"].ToString(),
                    Factory = Sdr["Factory"].ToString(),
                    Tel = Sdr["Tel"].ToString()
                });
                count += 1;
            }
            return result;
        }
    }
}
